﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlServerCe;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using Dapper;

namespace EasyInvoice.SqlCEDAL
{
    public class Database:IDatabase
    {
        public void Create()
        {
            SqlCeEngine engine = new SqlCeEngine(Properties.Settings.Default.ConnectionString);
            
            engine.CreateDatabase();

            try
            {
                using (System.Data.SqlServerCe.SqlCeConnection sqlConnection = new System.Data.SqlServerCe.SqlCeConnection(Properties.Settings.Default.ConnectionString))
                {

                    sqlConnection.Open();

                    foreach (string sql in EasyInvoice.SqlCEDAL.Properties.Resources.CreateDatabase.Replace("GO","").Replace("\r\n"," ").Split(new char[1]{';'}, StringSplitOptions.RemoveEmptyEntries))
                    {
                       if (!string.IsNullOrWhiteSpace(sql))  
                           sqlConnection.Execute(sql, commandTimeout: 0, commandType: CommandType.Text);
                    }
                    sqlConnection.Close();
                }
            }
            catch (Exception)
            {
                throw;
            }
        }

        public void test()
        {
            string connStr = "Data Source = Data\\data.sdf";

            SqlCeConnection conn = null;

            try
            {
                conn = new SqlCeConnection(connStr);
                conn.Open();

                SqlCeCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES;";
                SqlCeDataReader reader = cmd.ExecuteReader();
                // Iterate through the results
                //
                while (reader.Read())
                {
                    string lastName = reader["TABLE_NAME"].ToString();
                }

                // Always dispose data readers and commands as soon as practicable
                //
                reader.Close();
                cmd.Dispose();
            }
            catch { }
            finally
            {
                conn.Close();
            }

        }
    }
}
